clear all
cap close
set more off

cd ".\Data_Program"

use Econdata\sci,clear
drop if county1==county2

* (I) identify state
gen st1=floor(county1/1000)
gen st2=floor(county2/1000)
drop if inlist(st1,2,15)|st1>56
drop if inlist(st2,2,15)|st2>56


* (II) identify CZ
rename county1 county
merge m:1 county using Econdata\cz_county_crosswalk
drop if _merge==2
drop _merge
rename county county1
rename cz cz1

rename county2 county
merge m:1 county using Econdata\cz_county_crosswalk
drop if _merge==2
drop _merge
rename county county2
rename cz cz2

* (III) merge with distance
merge 1:1 county1 county2 using Econdata\sf12010countydistancemiles
drop if _merge==2
drop _merge


* indicating distant friends
gen outcz=1-(cz1==cz2 & cz1!=. & cz2!=.)     
drop cz1 cz2

gen outst=1-(st1==st2 & st1!=. & st2!=.)    
drop st1 st2

gen outdis100=(mi>100)   
gen outdis200=(mi>200)   

* keep closest 200 friends
gsort county1 -sci
by county1: keep if _n<=200
drop mi

expand 44   //2007-2017

sort county1 county2
by county1 county2: gen quarter=q(2007q1)+_n-1
format quarter %tq


* II. Merge with county-level finshare and other data
rename county2 county
merge m:1 county quarter using county
keep if _merge==3 
keep quarter county1 county finshare *share_refi* sci out*


* county finshare_ad
merge m:1 county quarter using Proprietary\mintel
drop if _merge==2 
drop _merge
replace finad=0 if finad==.
replace nonfinad=0 if nonfinad==.
replace ad=0 if ad==.
gen finadshare = finad/ad


    * CoreLogic house price growth
merge m:1 county quarter using Proprietary\hpi_county_quarter_exp
drop if _merge==2 
drop _merge qhpi
replace hpgr=hpgr4

    * Census population growth
gen year=yofd(dofq(quarter))
merge m:1 year county using Econdata\pop_county
drop if _merge==2  
drop _merge fracm frach pop


    * Census employment growth
merge m:1 year county using Econdata\emp_county_year
drop if _merge==2  
drop _merge year


    * Equifax CCP subprime share
merge m:1 quarter county using Proprietary\subprime_county_quarter
drop if _merge==2 
drop _merge 

     * Census education level in 2010
merge m:1 county using Econdata\edu
drop if _merge==2  
drop _merge hsabove

* III weighted average
sort quarter county1
by quarter county1: egen sumsci=sum(sci)
gen weight = sci/sumsci

foreach x of varlist finshare *share_refi* hpgr popgr fracy fracb empgr subprime finad nonfinad ad babove finadshare{
by quarter county1: egen `x'2=sum(weight*`x')
}
drop sumsci weight
 
* outcz 
by quarter county1: egen sumsci=sum(sci) if outcz==1
gen weight = sci/sumsci
by quarter county1: egen finshare2_outcz=sum(weight*finshare)
by quarter county1: egen finshare_refi2_outcz=sum(weight*finshare_refi)
by quarter county1: egen finad2_outcz=sum(weight*finad)
by quarter county1: egen finadshare2_outcz=sum(weight*finadshare)
by quarter county1: egen share_refi2_outcz=sum(weight*share_refi)
by quarter county1: egen nonbshare_refi2_outcz=sum(weight*nonbshare_refi)
by quarter county1: egen nbnfshare_refi2_outcz=sum(weight*nbnfshare_refi)
by quarter county1: egen bankshare_refi2_outcz=sum(weight*bankshare_refi)
drop sumsci weight

* outdis100
by quarter county1: egen sumsci=sum(sci) if outdis100==1
gen weight = sci/sumsci
by quarter county1: egen finshare2_outdis100=sum(weight*finshare)
by quarter county1: egen finad2_outdis100=sum(weight*finad)
drop sumsci weight

* outdis200
by quarter county1: egen sumsci=sum(sci) if outdis200==1
gen weight = sci/sumsci
by quarter county1: egen finshare2_outdis200=sum(weight*finshare)
by quarter county1: egen finad2_outdis200=sum(weight*finad)
drop sumsci weight

by quarter county1: keep if _n==1

keep county1 quarter *2 *2_out* 

rename county1 county

xtset county quarter
save sci_weighted_iv,replace